﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FJERP.Model;
using System.Web;
using System.Web.Script.Serialization;
using System.Collections;
using System.Data.SqlClient;
using System.Data;

namespace FJERP.DAL
{
    public class BlitemDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 库存盘点显示
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public ArrayList Blitem_Show(int PageIndex,int PageSize,out int PageCount)
        {
            SqlParameter[] Par ={
                    new SqlParameter("@PageIndex",PageIndex),
                    new SqlParameter("@PageSize",PageSize),
                    new SqlParameter("@PageCount",SqlDbType.Int),
                    };
            Par[2].Direction = ParameterDirection.Output;
            JavaScriptSerializer js = new JavaScriptSerializer();
            DataTable table = helper.ExecDataTable_Proc("Blitem_Show", Par);
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }
           
            PageCount = Convert.ToInt32(Par[2].Value);
            return list;
        }
        /// <summary>
        /// 库存的分页显示
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="PageCount"></param>
        /// <returns></returns>
        public ArrayList Stock_Show(int PageIndex, int PageSize, out int PageCount)
        {
            SqlParameter[] Par ={
                    new SqlParameter("@indext",PageIndex),
                    new SqlParameter("@size",PageSize),
                    new SqlParameter("@count",SqlDbType.Int),
                    };
            Par[2].Direction = ParameterDirection.Output;
            JavaScriptSerializer js = new JavaScriptSerializer();
            DataTable table = helper.ExecDataTable_Proc("Stock_Page", Par);
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }

            PageCount = Convert.ToInt32(Par[2].Value);
            return list;
        }
        /// <summary>
        /// 搜索
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="PageCount"></param>
        /// <param name="Name"></param>
        /// <returns></returns>
        public ArrayList SelectStoce(int PageIndex, int PageSize, out int PageCount,string Name)
        {
            SqlParameter[] Par ={
                    new SqlParameter("@indext",PageIndex),
                    new SqlParameter("@size",PageSize),
                    new SqlParameter("@count",SqlDbType.Int),
                    new SqlParameter("@name",Name)
                    };
            Par[2].Direction = ParameterDirection.Output;
            JavaScriptSerializer js = new JavaScriptSerializer();
            DataTable table = helper.ExecDataTable_Proc("Stock_search", Par);
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }

            PageCount = Convert.ToInt32(Par[2].Value);
            return list;
        }
        /// <summary>
        /// 盘点类型
        /// </summary>
        /// <returns></returns>
        public string GetPandianType()
        {
            string sql = "select Blitem_Type_Id,Blitem_Type_name from Blitem_Type";
            DataTable table = helper.ExecDataTable(sql);
            JavaScriptSerializer js = new JavaScriptSerializer();
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }
            return js.Serialize(list);
        }
        /// <summary>
        /// 添加盘点信息
        /// </summary>
        /// <param name="b"></param>
        /// <returns></returns>
        public int AddPandian(Blitem b)
        {
            string procname = "Pandian";
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@Stock_num",b.Stock_Num),
                new SqlParameter("@Blitem_Etc",b.Blitem_Etc),
                new SqlParameter("@WareHose_Id",b.WareHose_Id),
                new SqlParameter("@Blitem_num",b.Blitem_num),
                new SqlParameter("@Blitem_Rownum",b.Blitem_Rownum),
                new SqlParameter("@Blitem_Stock_num",b.Blitem_Stock_num),
                new SqlParameter("@Product_Id",b.Product_Id),
                new SqlParameter("@Blitem_Type_Id",b.Blitem_Type_Id)
            };
            return helper.ExecNonquery_Proc(procname, par);
        }
        /// <summary>
        /// 获取单行数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetStockById(int id)
        {
            string sql = "select Stock_Id,WareHose_Name,Product_Name,Stock_num,Stock.WareHose_Id,Stock.Product_Id from Stock join WareHose on Stock.WareHose_Id=WareHose.WareHose_Id join Product on Stock.Product_Id=Product.Product_Id where Stock_Id=" + id + "";
            DataTable table = helper.ExecDataTable(sql);
            JavaScriptSerializer js = new JavaScriptSerializer();
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }
            return js.Serialize(list);
        }
        /// <summary>
        /// 分页显示盘点单
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public ArrayList ShowBlitem(int PageSize, int PageIndex)
        {
            string sql = "select Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from (select ROW_NUMBER() over(order by Blitem_Id) as nid,Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from Blitem join WareHose on Blitem.WareHose_Id=WareHose.WareHose_Id join Product on Blitem.Product_Id=Product.Product_Id join Blitem_Type on Blitem.Blitem_Type_Id=Blitem_Type.Blitem_Type_Id) as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
         
            DataTable table = helper.ExecDataTable(sql);
            JavaScriptSerializer js = new JavaScriptSerializer();
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }
            return list;
        }
        public int Count()
        {
            string sql = "select count(*) from Blitem";
            return Convert.ToInt32(helper.ExecScalar(sql));
        }
        /// <summary>
        /// 分页显示盘点单
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public ArrayList SelectBlitem(int PageSize, int PageIndex, string Blitem_Etc)
        {
            string sql = "select Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from (select ROW_NUMBER() over(order by Blitem_Id) as nid,Blitem_Etc,WareHose_Name,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Name,Blitem_Type_name from Blitem join WareHose on Blitem.WareHose_Id=WareHose.WareHose_Id join Product on Blitem.Product_Id=Product.Product_Id join Blitem_Type on Blitem.Blitem_Type_Id=Blitem_Type.Blitem_Type_Id) as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + " and Product_Name like '%" + Blitem_Etc + "%'";

            DataTable table = helper.ExecDataTable(sql);
            JavaScriptSerializer js = new JavaScriptSerializer();
            ArrayList list = new ArrayList();
            foreach (DataRow item in table.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> dic = new Dictionary<string, object>();
                foreach (DataColumn cl in table.Columns)
                {
                    dic.Add(cl.ColumnName, item[cl.ColumnName]);
                }
                list.Add(dic);
            }
            return list;
        }
        public int Count(string Blitem_Etc)
        {
            string sql = "select count(*) from Blitem where Product_Name like '%" + Blitem_Etc + "%'";
            return Convert.ToInt32(helper.ExecScalar(sql));
        }
    }
}
